<?php	
//Connect to database
$con = mysql_connect("localhost", "ddp", "aFvbSq4dqQNbTaNf");
if(!$con)
{
    die('Could not connect to database: ' . mysql_error());
}
mysql_select_db("ddp", $con);
//Remember to close connection before stop
register_shutdown_function(function () {
    global $con;
    mysql_close($con);
});

/*
 * Query part
 *
 * query_... returns MySQL resource
 * get_... returns data directly (might be array)
 */

function query_data($start_time = null, $end_time = null, $device_id = null)
{
    global $con;
    $query = "select * from data where 1 = 1";
    if (!is_null($start_time) && !is_null($end_time))
    {
        $start_time = mysql_real_escape_string($start_time, $con);
        $end_time = mysql_real_escape_string($end_time, $con);
        $query .= " and Created_Time >= '$start_time' and Created_Time <= DATE_ADD('$end_time', INTERVAL 1 DAY)";
    }
    if (is_numeric($device_id))
    {
        $device_id = (int)$device_id;
        $query .= " and Device_ID = $device_id";
    }
    return mysql_query($query, $con);
}

function query_control($start_time = null, $end_time = null, $device_id = null)
{
    global $con;
    $query = "select * from control where 1 = 1";
    if (!is_null($start_time) && !is_null($end_time))
    {
        $start_time = mysql_real_escape_string($start_time, $con);
        $end_time = mysql_real_escape_string($end_time, $con);
        $query .= " and Created_Time >= '$start_time' and Created_Time <= DATE_ADD('$end_time', INTERVAL 1 DAY)";
    }
    if (is_numeric($device_id))
    {
        $device_id = (int)$device_id;
        $query .= " and Device_ID = $device_id";
    }
    return mysql_query($query, $con);
}

function query_device($user_id = null)
{
    global $con;
    $query = "select * from device where 1 = 1";
    if (is_numeric($user_id))
    {
        $user_id = (int)$user_id;
        $query .= " and User_ID = $user_id";
    }
    return mysql_query($query, $con);
}

function get_device($token = null, $id = null)
{
    global $con;
    $query = "select * from device where";
    if (!is_null($id))
    {
        $id = (int)$id;
        $query .= " Device_ID = $id";
    }
    else if (!is_null($token))
    {
        $token = mysql_real_escape_string($token, $con);
        $query .= " Token LIKE '$token'";
    }
    else
    {
        die("Neither token nor device_id is specified.");
    }
    $result = mysql_query($query, $con);
    $result = mysql_fetch_assoc($result);
    if (isset($result['ID'])) return $result;
    else return false;
}

function query_device_location($device_id)
{
    global $con;
    $device_id = (int)$device_id;
    $query = "select * from location where device_id = $device_id order by ID desc";
    $result = mysql_query($query, $con);
    return $result;
}

function get_device_location($device_id)
{
    global $con;
    $device_id = (int)$device_id;
    $query = "select * from location where device_id = $device_id order by ID desc limit 1";
    $result = mysql_query($query, $con);
    $result = mysql_fetch_assoc($result);
    if ($result != false) {
        $tmp['Latitude'] = $result['Latitude'];
        $tmp['Longitude'] = $result['Longitude'];
    }
    else $tmp = null;
    return $tmp;
}

function query_user($user_id = null)
{
    global $con;
    $query = "select * from user where 1 = 1";
    if (is_numeric($user_id))
    {
        $user_id = (int)$user_id;
        $query .= " and ID = $user_id";
    }
    return mysql_query($query);
}

function login_user($email, $password)
{
    global $con;
    $email = mysql_real_escape_string($email, $con);
    $password = sha1($password);
    $query = "select ID, Name, Email from user where Email = '$email' and Password = '$password'";
    $result = mysql_query($query);
    $result = mysql_fetch_assoc($result);
    if (isset($result['ID'])) return $result;
    else return false;
}


/*
 * Insert part
 *
 * Returns true/false (operation successful or not)
 */

function insert_data($device_id, $name, $type, $value, $unit = '')
{
    global $con;
    $device_id = (int)$device_id;
    $name = mysql_real_escape_string($name, $con);
    $type = mysql_real_escape_string($type, $con);
    $value = (float)$value;
    $unit = mysql_real_escape_string($unit, $con);
    $query = 'insert into data (Device_ID, Sensor_Name, Sensor_Type, Value, Unit)'
            ." values($device_id, '$name', '$type', $value, '$unit')";
    return mysql_query($query, $con);
}

function insert_location($device_id, $latitude, $longitude)
{
    global $con;
    $device_id = (int)$device_id;
    $latitude = (double)$latitude;
    $longitude = (double)$longitude;
    $query = 'insert into location (Device_ID, Latitude, Longitude)'
            ." values($device_id, $latitude, $longitude)";
    return mysql_query($query, $con);
}

function insert_control($device_id, $name, $command)
{
    global $con;
    $device_id = (int)$device_id;
    $name = mysql_real_escape_string($name, $con);
    $command = mysql_real_escape_string($command, $con);
    $query = 'insert into control (Device_ID, Sensor_Name, Command)'
            ." values($device_id, '$name', '$command')";
    return mysql_query($query, $con);
}

function add_user($email, $password, $name, $question, $answer)
{
    global $con;
    $email = mysql_real_escape_string($email, $con);
    $name = mysql_real_escape_string($name, $con);
    $question = mysql_real_escape_string($question, $con);
    $answer = mysql_real_escape_string($answer, $con);
    $password = sha1($password);
    $query = "insert into user (Email, Password, Name, Question, Answer)"
            ." values('$email', '$password', '$name', '$question', '$answer')";
    if (!mysql_query($query, $con)) return false;
    return mysql_insert_id($con);
}

function add_device($user_id, $device_name, $latitude, $longitude)
{
    global $con;
    $user_id = (int)$user_id;
    $device_name = mysql_real_escape_string($device_name, $con);
    $token = substr(sha1(uniqid('', true)), 0, 12);
    $query = "insert into device (User_ID, Device_Name, Token)"
            ." values($user_id, '$device_name', '$token')";
    if (!mysql_query($query, $con)) return false;
    $device_id = mysql_insert_id($con);
    if ($latitude != '' && $longitude != '')
    {
        insert_location($device_id, $latitude, $longitude);
    }
    return true;
}
